<?php
/**
* @file $Id: CustomFields.fnc.php 514 2007-05-14 07:24:52Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

/*
	Call in an SQL statement to select students based on custom fields
	
	Use in the from section of the query by CutomFields('from')
	
	Use in the where section of the query by CustomFIelds('where')
*/

function CustomFields($location,$table_arr='')
{	global $_FOCUS;
	if(count($_REQUEST['month_cust_begin']))
	{
		foreach($_REQUEST['month_cust_begin'] as $field_name=>$month)
		{
			$_REQUEST['cust_begin'][$field_name] = $_REQUEST['day_cust_begin'][$field_name].'-'.$month.'-'.$_REQUEST['year_cust_begin'][$field_name];
			$_REQUEST['cust_end'][$field_name] = $_REQUEST['day_cust_end'][$field_name].'-'.$_REQUEST['month_cust_end'][$field_name].'-'.$_REQUEST['year_cust_end'][$field_name];
			if(!VerifyDate($_REQUEST['cust_begin'][$field_name]) || !VerifyDate($_REQUEST['cust_end'][$field_name]))
			{
				unset($_REQUEST['cust_begin'][$field_name]);
				unset($_REQUEST['cust_end'][$field_name]);
			}
		}
		unset($_REQUEST['month_cust_begin']);unset($_REQUEST['year_cust_begin']);unset($_REQUEST['day_cust_begin']);
		unset($_REQUEST['month_cust_end']);unset($_REQUEST['year_cust_end']);unset($_REQUEST['day_cust_end']);
	}
	if(count($_REQUEST['cust']))
	{
		foreach($_REQUEST['cust'] as $key=>$value)
		{
			if($value=='')
				unset($_REQUEST['cust'][$key]);
		}
	}
	switch($location)
	{
		case 'from':
		break;
		
		case 'where':
		if(count($_REQUEST['cust']) || count($_REQUEST['cust_begin']))
			$fields = DBGet(DBQuery("SELECT TITLE,ID,TYPE FROM CUSTOM_FIELDS"),array(),array('ID'));
		
		if(count($_REQUEST['cust']))
		{
			foreach($_REQUEST['cust'] as $field_name => $value)
			{
				if($value!='')
				{
					switch($fields[substr($field_name,7)][1]['TYPE'])
					{
						case 'radio':
							$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].': </b></font>';
							if($value=='Y')
							{
								$string .= " and s.$field_name='$value' ";
								$_FOCUS['SearchTerms'] .= _('Yes');
							}
							elseif($value=='N')
							{
								$string .= " and (s.$field_name!='Y' OR s.$field_name IS NULL) ";
								$_FOCUS['SearchTerms'] .= _('No');
							}
							$_FOCUS['SearchTerms'] .= '<BR>';
						break;
						
						case 'select':
							$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].': </b></font>';
							if($value=='!')
							{
								$string .= " and (s.$field_name='' OR s.$field_name IS NULL) ";
								$_FOCUS['SearchTerms'] .= _('No Value');
							}
							else
							{
								$string .= " and s.$field_name='$value' ";
								$_FOCUS['SearchTerms'] .= $value;
							}
							$_FOCUS['SearchTerms'] .= '<BR>';
						break;

						case 'multiple':
							$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].': </b></font>';
							if($value=='!')
							{
								$string .= " and (s.$field_name='' OR s.$field_name IS NULL) ";
								$_FOCUS['SearchTerms'] .= _('No Value');
							}
							else
							{
								$string .= " and s.$field_name LIKE '%||$value||%' ";
								$_FOCUS['SearchTerms'] .= $value;
							}
							$_FOCUS['SearchTerms'] .= '<BR>';
						break;

						
						case 'text':
							if($value=='!')
							{
								$string .= " and (s.$field_name='' OR s.$field_name IS NULL) ";
								$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].': </b></font>'._('No Value').'<BR>';
							}
							elseif(substr($value,0,2)=='\"' && substr($value,-2)=='\"')
							{
								$string .= " and s.$field_name='".substr($value,2,-2)."' ";
								$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].': </b></font>'.substr($value,2,-2).'<BR>';
							}
							else
							{
								$string .= " and LOWER(s.$field_name) LIKE '".strtolower($value)."%' ";
								$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].' '._('starts with').': </b></font>'.$value.'<BR>';
							}
						break;
					}
				}
			}
		}
		if(count($_REQUEST['cust_begin']))
		{
			foreach($_REQUEST['cust_begin'] as $field_name => $value)
			{
				if($fields[substr($field_name,7)][1]['TYPE']=='numeric')
				{
					$_REQUEST['cust_end'][$field_name] = ereg_replace('[^0-9.-]+','',$_REQUEST['cust_end'][$field_name]);
					$value = ereg_replace('[^0-9.-]+','',$value);
				}

				if($_REQUEST['cust_begin'][$field_name]!='' && $_REQUEST['cust_end'][$field_name]!='')
				{
					if($fields[substr($field_name,7)][1]['TYPE']=='numeric' && $_REQUEST['cust_begin'][$field_name]>$_REQUEST['cust_end'][$field_name])
					{
						$temp = $_REQUEST['cust_end'][$field_name];
						$_REQUEST['cust_end'][$field_name] = $value;
						$value = $temp;
					}
					$string .= " and s.$field_name BETWEEN '$value' AND '".$_REQUEST['cust_end'][$field_name]."' ";
					if($fields[substr($field_name,7)][1]['TYPE']=='date')
						$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].' '._('between').': </b></font>'.ProperDate($value).' &amp; '.ProperDate($_REQUEST['cust_end'][$field_name]).'<BR>';
					else
						$_FOCUS['SearchTerms'] .= '<font color=gray><b>'.$fields[substr($field_name,7)][1]['TITLE'].' '._('between').': </b></font>'.$value.' &amp; '.$_REQUEST['cust_end'][$field_name].'<BR>';
				}
			}
		}

		if(count($_REQUEST['cust_log']))
		{
			foreach($_REQUEST['month_cust_log'] as $field_id=>$values)
			{
				foreach($values as $field_name=>$value)
				{
					//$field_id = substr($field_id,7);
					$_REQUEST['cust_log'][$field_id][$field_name]['end'] = $_REQUEST['day_cust_log'][$field_id][$field_name]['end'].'-'.$_REQUEST['month_cust_log'][$field_id][$field_name]['end'].'-'.$_REQUEST['year_cust_log'][$field_id][$field_name]['end'];
					$_REQUEST['cust_log'][$field_id][$field_name]['begin'] = $_REQUEST['day_cust_log'][$field_id][$field_name]['begin'].'-'.$_REQUEST['month_cust_log'][$field_id][$field_name]['begin'].'-'.$_REQUEST['year_cust_log'][$field_id][$field_name]['begin'];
					if(!VerifyDate($_REQUEST['cust_log'][$field_id][$field_name]['begin']) || !VerifyDate($_REQUEST['cust_log'][$field_id][$field_name]['end']))
					{
						unset($_REQUEST['cust_log'][$field_id][$field_name]['begin']);
						unset($_REQUEST['cust_log'][$field_id][$field_name]['end']);
					}
				
				}
			}
		
			foreach($_REQUEST['cust_log'] as $field_id=>$values)
			{
				if(!$_REQUEST['cust_log'][$field_id]['LOG_SELECT'] && !$_REQUEST['cust_log'][$field_id]['LOG_NUMERIC']['begin'] && !$_REQUEST['cust_log'][$field_id]['LOG_DATE']['date'])
					continue;
				//$field_id = substr($field_id,7);
				$string .= " and EXISTS (SELECT '' FROM STUDENT_LOG_ENTRIES sle WHERE sle.STUDENT_ID=ssm.STUDENT_ID AND sle.STUDENT_FIELD_ID='".substr($field_id,7)."' ";
				foreach($values as $field_name=>$value)
				{
					if(is_array($value))
					{
						if($field_name=='LOG_NUMERIC')
						{
							$_REQUEST['cust_log'][$field_id][$field_name]['end'] = ereg_replace('[^0-9.-]+','',$_REQUEST['cust_log'][$field_id][$field_name]['end']);
							$_REQUEST['cust_log'][$field_id][$field_name]['begin'] = ereg_replace('[^0-9.-]+','',$_REQUEST['cust_log'][$field_id][$field_name]['begin']);
						}
						elseif($field_name=='LOG_DATE')
						{}		
						
						if($_REQUEST['cust_log'][$field_id][$field_name]['begin']!='' && $_REQUEST['cust_log'][$field_id][$field_name]['end']!='')
						{
							if($field_name=='LOG_NUMERIC' && $_REQUEST['cust_log'][$field_id][$field_name]['begin']>$_REQUEST['cust_log'][$field_id][$field_name]['end'])
							{
								$temp = $_REQUEST['cust_log'][$field_id][$field_name]['end'];
								$_REQUEST['cust_log'][$field_id][$field_name]['end'] = $_REQUEST['cust_log'][$field_id][$field_name]['begin'];
								$_REQUEST['cust_log'][$field_id][$field_name]['begin'] = $temp;
							}
							$string .= " and sle.$field_name BETWEEN '".$_REQUEST['cust_log'][$field_id][$field_name]['begin']."' AND '".$_REQUEST['cust_log'][$field_id][$field_name]['end']."' ";
						}
					}
					else
						$string .= "AND sle.$field_name='$value' ";
				}
				$string .= ')';
			}
		}
		break;
	}
		return $string;
}
?>